
import sys ,os
import json
import urllib.parse
import uuid
import argparse
import requests
from datetime import datetime
from pylibs import SqlHelper
from pylibs import SeleniumUtil
from pylibs import HttpUtil
from pylibs import IdCardUtil
from pylibs import ImageUtil
from pylibs import OcrUtil
from pylibs import DatetimeUtil
import xlrd
import pandas as pd
import threading
from threading import Timer  
import time  
import base64  
import io  
from PIL import Image  
import random

import urllib
from selenium.webdriver.common.action_chains import ActionChains  
from selenium.webdriver.common.by import By  

import logging  
logging.basicConfig(level=logging.INFO)  



class ToolConfig:
    
    task_id=None
    config_json_file="config.json"
    configJson=None

    tool_name='ybtool1'
    task_name='人员结算明细采集'

    yb1siteUrl="http://heb.hsip.gov.cn"
    yb1siteSession=None 
    yb1siteToken = None

    yb2siteUrl ="http://ld.heb.hsip.gov.cn"
    yb2siteSession=None
    yb2siteToken=None

    chromeDriver=None

    dtBenqiBeginTime=None
    dtBenqiEndTime=None
    dtShangqiBeginTime=None
    dtShangqiEndTime=None

    doGrabBendiMingxiJixu="y"
    doGrabAllMingxiJixu="y"

    TaskStatusJson={
        # 消息
        "msg": '',
        # 本地结算明细的机构id
        "bendiMingxiJindu":{},
        # 报表子系统的最新采集人员id
        "allMingxiPsnNo":"",
    }

    # insutype 和 insutypeName 的对应关系字典
    insutype_mapping = {
        '300': '补充百分之10医疗',
        '310': '职工基本医疗保险',
        '320': '公务员医疗补助',
        '330': '大额医疗费用补助',
        '340': '离休人员医疗保障',
        '350': '长期照护保险',
        '370': '企业补充医疗保险',
        '380': '新型农村合作医疗',
        '390': '城乡居民基本医疗保险',
        '391': '城镇居民基本医疗保险',
        '392': '城乡居民大病医疗保险',
        '399': '职工补充医疗保险',  # 399对应两个名称，确保逻辑处理得当
        '510': '生育保险',
        '611': '城乡医疗救助',
        '612': '优抚对象医疗补助'
    }

    # 反向字典，从名称到代码的映射
    insutype_reverse_mapping = {v: k for k, v in insutype_mapping.items()}

    # medType 和 medTypeName 的对应关系字典
    medType_mapping = {
        '11': '普通门诊',
        '12': '门诊挂号',
        '13': '急诊',
        '14': '门诊慢特病',
        '21': '普通住院',
        '22': '外伤住院',
        '23': '转外诊治住院',
        '24': '急诊转住院',
        '41': '定点药店购药',
        '51': '生育门诊',
        '52': '生育住院',
        '53': '计划生育手术费',
        '91': '其他门诊',
        '92': '其他住院',
        '93': '其他购药',
        '99': '地方扩展医疗类别',
        '9103': '外购',
        '9216': '双通道住院'
    }

    # 反向字典，从名称到代码的映射
    medType_reverse_mapping = {v: k for k, v in medType_mapping.items()}

    shengJson =None
    shiJson = None 
    xianJson=None



# 工具启动，加载参数，初始化若干参数
def init_tool():
    parser = argparse.ArgumentParser(description='ybtool1 参数')  
  
    # 添加一个命令行参数 -c 或 --config，类型为字符串，默认值为 None  
    parser.add_argument('-c', '--config', type=str, help='配置文件信息')  
  
    # 解析命令行参数  
    args = parser.parse_args()  

    if args.config is not None:
        ToolConfig.config_json_file = args.config

    # 判断 config_json_file 是否存在  
    if os.path.exists(ToolConfig.config_json_file):  
        # 如果 config_json_file 存在，则加载到 configJson 中  
        with open(ToolConfig.config_json_file, 'r', encoding='utf-8') as file:  
            ToolConfig.configJson = json.load(file)  
           
        logging.info(f'装载配置文件 {ToolConfig.config_json_file} 成功')  

        # 检查 config_json_file 中的参数信息
        ## 初始化任务id， 格式为uuid 
        ToolConfig.task_id = ToolConfig.configJson['task_id'] if 'task_id' in  ToolConfig.configJson and ToolConfig.configJson['task_id'] else uuid.uuid4().hex


    else:  
        # 如果 config_json_file 不存在，则返回提示信息  
        logging.error(f'程序终止。原因：配置文件 {ToolConfig.config_json_file} 不存在, 无法执行数据采集。') 
        exit(0)

    # 装载省市县的json
    with open('./sheng.json',encoding='utf-8') as fsheng:
        srcShengJson=json.load(fsheng)
        ToolConfig.shengJson= {item['code']: item['name'] for item in srcShengJson}
    with open('./shi.json',encoding='utf-8') as fshi:
        srcShiJson=json.load(fshi)
        ToolConfig.shiJson= {item['code']: item['name'] for item in srcShiJson}
    with open('./xian.json',encoding='utf-8') as fxian:
        srcXianJson=json.load(fxian)
        ToolConfig.xianJson= {item['code']: item['name'] for item in srcXianJson}

        
def initSqlHelper():
    SqlHelper.dbinfo['host']= ToolConfig.configJson['db_host']
    SqlHelper.dbinfo['port']= ToolConfig.configJson['db_port']
    SqlHelper.dbinfo['user']= ToolConfig.configJson['db_user']
    SqlHelper.dbinfo['passwd']= ToolConfig.configJson['db_passwd']
    SqlHelper.dbinfo['db']= ToolConfig.configJson['db_database']
    

def checkConfigJson():
    
    ## 检查 chromedriver_path 是否存在
    chromeDriverPath = ToolConfig.configJson['chromedriver_path']
    if not os.path.exists(chromeDriverPath):
        logging.error(f"程序终止。原因： config_json文件的 chromedriver_path 指向的文件<{chromeDriverPath}>不存在。")
        exit(0)

    strTimeFormat="%Y-%m-%d %H:%M:%S"

    strBenqiBeginTime=ToolConfig.configJson['benqi_begin_time']
    ToolConfig.dtBenqiBeginTime = DatetimeUtil.str2dt(strBenqiBeginTime)
    if ToolConfig.dtBenqiBeginTime is None:
        logging.error(f"程序终止。原因：config.json的 benqi_begin_time 的值{strBenqiBeginTime},格式必须为 <{strTimeFormat}>")
        exit(0)

    strBenqiEndTime=ToolConfig.configJson['benqi_end_time']
    ToolConfig.dtBenqiEndTime = DatetimeUtil.str2dt(strBenqiEndTime)
    if ToolConfig.dtBenqiEndTime is None:
        logging.error(f"程序终止。原因：config.json的 benqi_end_time 的值{strBenqiEndTime},格式必须为 <{strTimeFormat}>")
        exit(0)

    strShangqiBeginTime=ToolConfig.configJson['shangqi_begin_time']
    ToolConfig.dtShangqiBeginTime = DatetimeUtil.str2dt(strShangqiBeginTime)
    if ToolConfig.dtShangqiBeginTime is None:
        logging.error(f"程序终止。原因：config.json的 shangqi_begin_time 的值{strShangqiBeginTime},格式必须为 <{strTimeFormat}>")
        exit(0)

    strShangqiEndTime=ToolConfig.configJson['shangqi_end_time']
    ToolConfig.dtShangqiEndTime = DatetimeUtil.str2dt(strShangqiEndTime)
    if ToolConfig.dtShangqiEndTime is None:
        logging.error(f"程序终止。原因：config.json的 shangqi_end_time 的值{strShangqiEndTime},格式必须为 <{strTimeFormat}>")
        exit(0)

    ## 检查本期结算总额数据的excel文件
    strBenqiAllExcelPath=ToolConfig.configJson['benqi_all_excel']
    if not os.path.exists(strBenqiAllExcelPath):
        logging.error(f"程序终止。原因：config_json文件的benqi_all_excel指向的文件<{strBenqiAllExcelPath}>不存在")
        exit(0)

    ## 检查上期结算总额数据的excel文件
    strShangqiAllExcelPath=ToolConfig.configJson['shangqi_all_excel']
    if not os.path.exists(strShangqiAllExcelPath):
        logging.error(f"程序终止。原因：config_json文件的shangqi_all_excel指向的文件<{strShangqiAllExcelPath}>不存在")
        exit(0)

    ## 检查本期异地结算明细的excel文件
    strBenqiYidiMingxiExcelPath=ToolConfig.configJson['benqi_yidi_mingxi_excel']
    if not os.path.exists(strBenqiYidiMingxiExcelPath):
        logging.error(f"程序终止。原因：config_json文件的 benqi_yidi_mingxi_excel 指向的文件<{strBenqiYidiMingxiExcelPath}>不存在")
        exit(0)

    ## 检查本期本地机构信息数组，包括机构id、登录账号和登录密码
    if "bendi_jigou_infos" not in ToolConfig.configJson:
        logging.error(f"程序终止。原因：config_json文件的 bendi_jigou_infos 为空")
        exit(0)
    
    arrBendiJigouInfo=ToolConfig.configJson['bendi_jigou_infos']
    for bendiJigouInfo in    arrBendiJigouInfo:
        if 'jigou_id' not in bendiJigouInfo or 'login_code' not in bendiJigouInfo or 'login_pwd' not in bendiJigouInfo:
            logging.error(f"程序终止。原因：config_json文件的 bendi_jigou_infos中，所有机构信息必须包括 jigou_id login_code login_pwd")
            exit(0)

    
def clear_yidi_mingxi_excel():

    ### 删除 任务相关的 异地结算明细数据
    sqlDel8= "delete from t_tmp_ybtool1_yidi_mingxi_excel where grab_task_id=%s"
    SqlHelper.delete(sql=sqlDel8, args= (ToolConfig.task_id ) )

def clear_tmp_bendi_mingxi():
    ### 删除 任务相关的 本地结算明细临时数据表 的数据 
    sqlDel8= "delete from t_tmp_ybtool1_bendi_mingxi where grab_task_id=%s"
    SqlHelper.delete(sql=sqlDel8, args= (ToolConfig.task_id ) )

def clear_all_excel():
    ## 清理所有相关的临时数据
    ### 删除 任务相关的 报表子系统的结算数据
    sqlDel9 = "delete from t_tmp_ybtool1_all_excel2 where grab_task_id =%s "
    SqlHelper.delete(sql=sqlDel9, args= (ToolConfig.task_id ) )

# 准备好 chromedriver 登录后获得 token
def initChromeDriver():
    ToolConfig.chromeDriver=SeleniumUtil.initWebDriver(ToolConfig.configJson['chromedriver_path'])

# 第1步创建任务
def step1_create_task():
    
    # 2 保存任务
    SqlHelper.connect()

    # 获取当前时间  
    now9 = datetime.now()  
    
    # 格式化当前时间为 yyyy-MM-dd HH:mm:ss 格式  
    formatted_now = now9.strftime('%Y-%m-%d %H:%M:%S') 

    cnt1 = SqlHelper.fetch_one('select count(1) as cnt from t_grab_task where id = %s',(ToolConfig.task_id))
    
    statusJson8= ToolConfig.TaskStatusJson
    param9={"taskId":ToolConfig.task_id, "taskName":f"{formatted_now}发起的{ToolConfig.task_name}",
                "configJson":json.dumps(ToolConfig.configJson),
                "toolName":ToolConfig.tool_name,
                "statusJson": json.dumps(statusJson8) ,}
    
    if cnt1['cnt'] > 0 :
        sql9="update t_grab_task set task_name =%(taskName)s ,task_config_json =%(configJson)s ,\
                         tool_name=%(toolName)s where id =%(taskId)s"
        
        SqlHelper.update(sql9, param9)

        # 装载 status_json
        loadTaskStatusJson()
    else:
        sql9="insert into t_grab_task (id,task_name,task_config_json,tool_name,grab_time,\
                     status_json)\
                     values(%(taskId)s,%(taskName)s,%(configJson)s,%(toolName)s,now(),%(statusJson)s)"
        SqlHelper.insert(sql9, param9)

    

# 执行更新TaskInfo字段的操作
def updateTaskStatusInfo(  ):
    sql9="update t_grab_task set status_json=%(statusJson)s where id = %(taskId)s"

    statusJson8= ToolConfig.TaskStatusJson
    
    param9={"statusJson":json.dumps(statusJson8),"taskId":ToolConfig.task_id}
    SqlHelper.update( sql9, param9 )

def loadTaskStatusJson():
    sql9="select status_json from t_grab_task where id = %(taskId)s"
    param9={"taskId":ToolConfig.task_id}
    res9=SqlHelper.fetch_one(sql9,param9)
    if res9 is not None:
        try:
            json9= json.loads(res9['status_json'])
        except:
            json9={}

        ToolConfig.TaskStatusJson['msg'] = json9['msg'] if "msg" in json9 else ''
        ToolConfig.TaskStatusJson['bendiMingxiJindu'] = json9['bendiMingxiJindu'] if 'bendiMingxiJindu' in json9 else {}
        ToolConfig.TaskStatusJson['allMingxiPsnNo']= json9['allMingxiPsnNo'] if 'allMingxiPsnNo' in json9 else ''

def _grab_benqi_all_jiesuan_excel2(excelPath):
    ## 3.2 判断excelPath是否存在
    if not os.path.exists(excelPath):
        strErr=f"程序终止。原因：本期报表子系统的结算总额Excel未找到，\
请检查<{excelPath}>文件是否存在。"
        logging.error(strErr)
        exit(0)

    ## 3.1 获取sheet清单
    logging.info(f"==开始：加载本期结算总额数据文件csv文件 -->{excelPath}")

    try:
        wb1=xlrd.open_workbook(excelPath)
        sheetNames=wb1.sheet_names()
    except Exception as err1:
        logging.error(f" ERROR:ERROR:ERROR:ERROR:ERROR:ERROR:ERROR \n 请检查你的文件{excelPath},其文件格式并非xlsx格式的。可能的问题： 文件格式是文本的csv文件。")
        exit(0)

    logging.info(f"==开始：采集本期结算总额数据csv文件，共<{len(sheetNames)}>个sheet页。")
    
    for sheetName in sheetNames:
        data1 = pd.read_excel(excelPath, sheet_name=sheetName, header = 3)
        currSheetRowCnt=len(data1)
        
        # 将数据遍历保存到数据表 t_tmp_ybtool1_all_excel 
        insertPiliangCnt = 20
        arrSqlParam=[]
        for idx1,d1 in data1.iterrows():

            sql9 = "insert into t_tmp_ybtool1_all_excel2 \
            (id,grab_task_id, psnNo,benqi_money)\
                    values (%s,%s,%s,%s ) "
            tid1= uuid.uuid4().hex
            param9=(tid1, ToolConfig.task_id,
                        d1.iloc[4] if not pd.isna(d1.iloc[4] ) else '' ,
                        d1.iloc[7] if not pd.isna(d1.iloc[7] ) else '' , )
            
            arrSqlParam.append( (sql9,param9))

            # 如果数量达到批量提交的次数，则提交
            if len(arrSqlParam)>=insertPiliangCnt:
                SqlHelper.batch_execute( arrSqlParam )
                arrSqlParam.clear()

            if (idx1+1)%1000 == 0 :
                logging.info(f"++正在：采集本期结算总额数据，<{sheetName}/{idx1+1}/{currSheetRowCnt}>")

        if len(arrSqlParam)>0:
            SqlHelper.batch_execute( arrSqlParam )
            arrSqlParam.clear()

def _grab_shangqi_all_jiesuan_excel2(excelPath):
    ## 3.2 判断excelPath是否存在
    if not os.path.exists(excelPath):
        strErr=f"程序终止。原因：上期报表子系统的结算总额Excel未找到，\
请检查<{excelPath}>文件是否存在。"
        logging.error(strErr)
        exit(0)

    ## 3.1 获取sheet清单
    logging.info(f"==开始：加载上期结算总额数据文件csv文件 -->{excelPath}")

    try:
        wb1=xlrd.open_workbook(excelPath)
        sheetNames=wb1.sheet_names()
    except Exception as err1:
        logging.error(f" ERROR:ERROR:ERROR:ERROR:ERROR:ERROR:ERROR \n 请检查你的文件{excelPath},其文件格式并非xlsx格式的。可能的问题： 文件格式是文本的csv文件。")
        exit(0)

    logging.info(f"==开始：采集上期结算总额数据csv文件，共<{len(sheetNames)}>个sheet页。")
    
    for sheetName in sheetNames:
        data1 = pd.read_excel(excelPath, sheet_name=sheetName, header = 3)
        currSheetRowCnt=len(data1)

        insertPiliangCnt = 20
        arrSqlParam=[]
        for idx1,d1 in data1.iterrows():
        
            # 将数据遍历保存到数据表 t_tmp_ybtool1_all_excel 
            shangqiMoney=d1.iloc[7] if not pd.isna(d1.iloc[7] ) else ''
            psnNo=d1.iloc[4] if not pd.isna(d1.iloc[4] ) else ''
            taskId=ToolConfig.task_id
            sql9 = "update t_tmp_ybtool1_all_excel2 set shangqi_money=%(shangqiMoney)s \
                where grab_task_id=%(taskId)s and  psnNo=%(psnNo)s "
            param9={"taskId":taskId,"psnNo":psnNo,"shangqiMoney":shangqiMoney}
            arrSqlParam.append( (sql9,param9 ) )

            if len(arrSqlParam) >= insertPiliangCnt:
                SqlHelper.batch_execute(arrSqlParam)
                arrSqlParam.clear()

            if (idx1+1)%1000 == 0 :
                logging.info(f"++正在：采集上期结算总额数据，<{sheetName}/{idx1+1}/{currSheetRowCnt}>")

        if len(arrSqlParam) >= insertPiliangCnt:
                SqlHelper.batch_execute(arrSqlParam)
                arrSqlParam.clear()

# 已废弃
# def _grab_all_jiesuan_excel(qilevel,beginDate,endDate,excelPath):

#     ## 3.2 判断excelPath是否存在
#     if not os.path.exists(excelPath):
#         strErr=f"程序终止。原因：{qilevel}报表子系统的结算总额Excel未找到，\
# 请检查<{excelPath}>文件是否存在。"
#         logging.error(strErr)
#         exit(0)


#     ## 3.1 获取sheet清单
#     logging.info(f"==开始：加载{qilevel}结算总额数据文件csv文件 -->{excelPath}")

#     wb1=xlrd.open_workbook(excelPath)
#     sheetNames=wb1.sheet_names()

#     logging.info(f"==开始：采集{qilevel}结算总额数据csv文件，共<{len(sheetNames)}>个sheet页。")
    
#     for sheetName in sheetNames:
#         data1 = pd.read_excel(excelPath, sheet_name=sheetName, header = 3)
#         currSheetRowCnt=len(data1)
        
#         # 将数据遍历保存到数据表 t_tmp_ybtool1_all_excel 
#         cnt3=0
#         sqlInsert = "insert into t_tmp_ybtool1_all_excel (id,grab_task_id,qi_level,begin_date,end_date,\
#             csv_col1,csv_col2,csv_col3,csv_col4,csv_col5,csv_col6,csv_col7,csv_col8,csv_col9,\
#                 csv_col10,csv_col11,csv_col12,csv_col13,csv_col14,csv_col15)\
#                     values"
#         insertPiliangCnt = 20
#         for idx1,d1 in data1.iterrows():

#             if cnt3==0:
#                 sqlInsert3 = sqlInsert
#                 insertParam3 = ()
            
#             sqlInsert3+="(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s),"
#             tid1= uuid.uuid4().hex
#             insertParam3+=(tid1, ToolConfig.task_id,qilevel,beginDate,endDate )
            
#             for i3 in range(15):
#                 insertParam3+=( d1.iloc[i3] if not pd.isna(d1.iloc[i3] ) else '' , )
#             cnt3+=1

#             # 如果数量达到批量提交的次数，则提交
#             if cnt3>=insertPiliangCnt:
#                 cnt3=0
#                 sqlInsert3 = sqlInsert3[0:len(sqlInsert3)-1]
#                 SqlHelper.insert(sqlInsert3,insertParam3)

#             if (idx1+1)%1000 == 0 :
#                 logging.info(f"++正在：采集{qilevel}结算总额数据，<{sheetName}/{idx1+1}/{currSheetRowCnt}>")

#         if cnt3>0:
#             sqlInsert3 = sqlInsert3[0:len(sqlInsert3)-1]
#             SqlHelper.insert(sqlInsert3,insertParam3)
            
    
# 第2步： 采集本期结算总额的数据
def step2_grab_benqi_all_execl():
  
    # 1 获得本期的excel 文件路径
    strExcelFile=ToolConfig.configJson['benqi_all_excel']
    
    if not os.path.exists(strExcelFile):
        strErr=f"程序终止。原因：本期报表子系统的结算总额Excel未找到，\
请检查<{ToolConfig.config_json_file}>的<benqi_all_excel>的值。"
        logging.error(strErr)
        exit(0)

    # 2 采集本期所有结算信息
    _grab_benqi_all_jiesuan_excel2(strExcelFile)            
    

    logging.info(f"==完成：采集本期结算总额数据csv文件")

# 第3步，采集上期结算总额数据
def step3_grab_shangqi_all_execl():

    # 1 获得上期的excel 文件路径
    strExcelFile=ToolConfig.configJson['shangqi_all_excel']
    
    if not os.path.exists(strExcelFile):
        strErr=f"程序终止。原因：上期报表子系统的结算总额Excel未找到，\
请检查<{ToolConfig.config_json_file}>的<shangqi_all_excel>的值。"
        logging.error(strErr)
        exit(0)

    _grab_shangqi_all_jiesuan_excel2(strExcelFile)            
    
    logging.info(f"==完成：采集shangqi结算总额数据csv文件")

    return 0

# 第4步，采集本期异地结算明细数据
def step4_grab_benqi_yidi_mingxi_execl():

    # 获取 ToolConfig.configJson.benqi_yidi_mingxi_excel 的文件
    strBenqiYidiMingxiExcelPath=ToolConfig.configJson['benqi_yidi_mingxi_excel']

    # 2 获取本期的开始和结束时间，并转为时间类型
    strGrabBeginDate= DatetimeUtil.dt2str(ToolConfig.dtShangqiEndTime)
    strGrabEndDate=DatetimeUtil.dt2str( ToolConfig.dtBenqiEndTime )

    # 读取文件的数据

    logging.info(f"==开始：采集本期异地结算明细数据文件<{strBenqiYidiMingxiExcelPath}>。")

    try:    
        data8 = pd.read_excel(strBenqiYidiMingxiExcelPath, header = 0)
        currSheetRowCnt=len(data8)
    except Exception as err1:
        logging.error(f" ERROR:ERROR:ERROR:ERROR:ERROR:ERROR:ERROR \n 请检查你的文件{strBenqiYidiMingxiExcelPath},其文件格式并非xlsx格式的。")
        exit(0)

    # 将数据遍历保存到数据表 t_tmp_ybtool1_yidi_mingxi_excel 
    

    insertPiliangCnt = 30
    xlsxColCnt = 57

    arrSqlParam=[]

    for idx1,d1 in data8.iterrows():
        
        sql9 = "insert into t_tmp_ybtool1_yidi_mingxi_excel (id,grab_task_id,begin_date,end_date"
        for colIdx8 in range(xlsxColCnt):
            sql9 += f", xlsx_col{colIdx8+1}"
        sql9 += ")values ("

        for colIdx7 in range(xlsxColCnt+4):
            sql9 += f"%s,"
        sql9=sql9[0:len(sql9)-1]
        sql9+=")"

        tid1= uuid.uuid4().hex
        param9 = (tid1, ToolConfig.task_id,strGrabBeginDate,strGrabEndDate )
        
        for i3 in range(xlsxColCnt):
            param9+=( d1.iloc[i3] if  not pd.isna(d1.iloc[i3]) else '' ,   )

        arrSqlParam.append( (sql9, param9) )

        # 如果数量达到批量提交的次数，则提交
        if len(arrSqlParam )>=insertPiliangCnt:
            SqlHelper.batch_execute( arrSqlParam )
            arrSqlParam.clear()

        if (idx1+1)%1000 == 0 :
            logging.info(f"++正在：采集本期异地结算明细数据，<{idx1+1}/{currSheetRowCnt}>")

    if len(arrSqlParam ) >0:
        SqlHelper.batch_execute( arrSqlParam )
        arrSqlParam.clear()

    return 0

  
def timed_input(prompt, timeout, default=''):  
    """  
    模拟带超时的input函数，如果超时则返回默认值  
    :param prompt: 显示的提示信息  
    :param timeout: 超时时间（秒）  
    :param default: 超时后的默认值  
    :return: 用户输入或默认值  
    """  
    class InputThread(threading.Thread):  
        def __init__(self, prompt):  
            super().__init__()  
            self.prompt = prompt  
            self.result = None  
  
        def run(self):  
            self.result = input(self.prompt)  
  
    thread = InputThread(prompt)  
    thread.start()  
    thread.join(timeout)  
    if thread.is_alive():  
        print(f'\nTimeout: {timeout} seconds reached. Using default value.')  
        return default  
    else:  
        return thread.result  
  

# 第5步，采集本期本地结算数据
def step11_grab_benqi_bendi_mingxi():

    min1 = ToolConfig.configJson['grab_min_interval']
    max1 = ToolConfig.configJson['grab_max_interval']

    strGrabBeginDate= DatetimeUtil.dt2str(ToolConfig.dtShangqiEndTime)
    strGrabEndDate=DatetimeUtil.dt2str( ToolConfig.dtBenqiEndTime )

    print("============================")
    n1 = 0
    for jigouInfo in ToolConfig.configJson['bendi_jigou_infos']:
        n1+=1
        print(f"=={n1} {jigouInfo['jigou_id']}==")
    print("============================")

    strJigouNumb = timed_input(" ======= 请输入计划采集的顶点医疗机构的顺序号(0或不填则为全部)：", timeout=20, default="0")
    jigouCode = ''
    if strJigouNumb!="" and strJigouNumb!='0':
        try:
            intJigouNumb = int(strJigouNumb)
            jigou2= ToolConfig.configJson['bendi_jigou_infos'][intJigouNumb-1]
            if jigou2 is not None:
                jigouCode = jigou2['jigou_id']
        except:
            print("")
    
    for jigouInfo in ToolConfig.configJson['bendi_jigou_infos']:

        pageNumb=1
        
        jigouId=jigouInfo['jigou_id']
        loginCode=jigouInfo['login_code']
        loginPwd = jigouInfo['login_pwd']
        
        if jigouCode !='' and jigouCode != jigouId:
            print(f" ========= 跳过{jigouId}的采集")
            continue

        # 如果继续之前的进度采集
        if ToolConfig.doGrabBendiMingxiJixu !='n':
            jinduJson = ToolConfig.TaskStatusJson['bendiMingxiJindu']

            if loginCode in jinduJson:
                pageNumb = jinduJson[loginCode]+1

        _ld_login(loginCode= loginCode, loginPwd= loginPwd)

        if ToolConfig.yb2siteSession is None:
            logging.warn(f"用户 {loginCode} 登录失败，无法获得token信息")
            continue
        
        grabPageCount=0

        # 登录成功后，开始抓取接口, 循环一页一页的抓取
        logging.info(f"====== 开始采集定点机构的结算明细数据")

        while True:

            str18="curl 'http://ld.heb.hsip.gov.cn/fms/web/queryInfo/queryDaySetlInfoPage' \
                    -H 'Accept: application/json, text/plain, */*' \
                    -H 'Accept-Language: zh-CN,zh;q=0.9,en;q=0.8,en-GB;q=0.7,en-US;q=0.6' \
                    -H 'Connection: keep-alive' \
                    -H 'Content-Type: application/x-www-form-urlencoded; charset=UTF-8' \
                    -H 'Cookie: XSRF-TOKEN=#token1#; SESSION=#session1#' \
                    -H 'Origin: http://ld.heb.hsip.gov.cn' \
                    -H 'Referer: http://ld.heb.hsip.gov.cn/fms/ui/W15.html' \
                    -H 'User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/125.0.0.0 Safari/537.36 Edg/125.0.0.0' \
                    -H 'X-XSRF-TOKEN: #token1#' \
                    --data-raw 'pageNum=#pageNum#&pageSize=40&begndate=#begindate1#&enddate=#enddate1#&_modulePartId_=&frontUrl=http%3A%2F%2Fld.heb.hsip.gov.cn%2Ffms%2Fui%2FW15.html%23%2FW1502%3Fhtml_no_cache%3D1716516625499' \
                    --insecure"
            
            str18 = str18.replace('#pageNum#',str(pageNumb))
            str18 = str18.replace('#token1#', ToolConfig.yb2siteToken )
            str18 = str18.replace('#session1#', ToolConfig.yb2siteSession )
            str18 = str18.replace('#begindate1#',  strGrabBeginDate )
            str18 = str18.replace('#enddate1#', strGrabEndDate)
            
            res18=HttpUtil.curl2requests(str18)

            tmsleep = random.randrange(min1,max1)/1000.0

            requestSuccess = False

            # 重新采集3次
            errorSleep = 10
            retryN = 3
            for retry in range(retryN):

                try:
                    
                    resp18=requests.request(
                        method=res18.method,
                        url=res18.fullurl,
                        data=res18.data,
                        json=res18.json,
                        headers=res18.headers
                    )
                except Exception as err1:
                    logging.warn(f"执行失败，稍后重试。原因：请求定点医疗机构{jigouId}结算明细数据第{pageNumb}页出现异常")
                    time.sleep(errorSleep)
                    continue
                    

                if resp18.status_code != 200:
                    logging.warn(f"执行失败，稍后重试。原因：请求定点医疗机构{jigouId}结算明细数据，response.status_code={resp18.status_code}， {resp18.text}")
                    time.sleep(errorSleep)
                    continue
                else:
                    
                    strjson = resp18.text
                    json18 = json.loads(strjson)

                    if json18['code']!=0 or json18['type']!='success':
                        logging.warn(f"请求{jigouId}的结果：{json18}")
                        
                        time.sleep(errorSleep)
                        continue
                    else:
                        # 请求是否成功
                        requestSuccess = True

                        pageCount8 = json18['data']['pageBean']['pages']
                        recordCounts8 = json18['data']['pageBean']['recordCounts']

                        data18s = json18['data']['pageBean']['data']

                        _save_bendi_to_tmp_ybtool1_bendi_mingxi(data18s)

                        _save_bendi_to_ybtool1_jiesuan_mingxi(data18s)

                        logging.info(f"*****采集定点机构{jigouId}结算明细数据 第{pageNumb}/{pageCount8}页，共{recordCounts8}条记录")
                        break 

            
            if not requestSuccess:
                logging.error(f"XXXXXXX 采集定点机构{jigouId}结算明细数据 第{pageNumb}页， {retryN}次尝试均失败。停止继续采集，该问题请及时联系技术人员处理。")
                break


            ToolConfig.TaskStatusJson['bendiMingxiJindu'][loginCode] = pageNumb
            updateTaskStatusInfo()

            grabPageCount+=1

            if "maxGrabCount" in  ToolConfig.configJson and \
                ToolConfig.configJson['maxGrabCount'] >0 and \
                grabPageCount >= ToolConfig.configJson['maxGrabCount']:
                break

            if pageCount8 <= pageNumb:
                break
            
            pageNumb+=1

            time.sleep(tmsleep)

            ### 测试代码
            # breakNumb = 5
            # if pageNumb > breakNumb :
            #     print(f"### 测试代码，pageNumb > {breakNumb} 则跳出当前机构的采集")
            #     break

        _ld_logout()
        # 每次注销后，休眠2秒钟
        time.sleep(2)


    return 0



def _save_bendi_to_tmp_ybtool1_bendi_mingxi(arrData):
    for data in arrData:
        lb_acctMulaidPay = data['acctMulaidPay']
        lb_acctPay = data['acctPay']
        lb_actPayDedc = data['actPayDedc']
        lb_afilIndu = data['afilIndu']
        lb_afilRlts = data['afilRlts']
        lb_age = data['age']
        lb_bSetlId = data['bSetlId']
        lb_balc = data['balc']
        lb_begndate = DatetimeUtil.msstamp2str(data['begndate'], '%Y-%m-%d %H:%M:%S') if data['begndate'] is not None else None
        lb_brdy = data['brdy']
        lb_calIptCnt = data['calIptCnt']
        lb_cashPayamt = data['cashPayamt']
        lb_certno = data['certno']
        lb_clctGrde = data['clctGrde']
        lb_clrAppyEvtId = data['clrAppyEvtId']
        lb_clrOptins = data['clrOptins']
        lb_clrType = data['clrType']
        lb_clrWay = data['clrWay']
        lb_crtDedc = data['crtDedc']
        lb_crteOptinsNo = data['crteOptinsNo']
        lb_crteTime = DatetimeUtil.msstamp2str(data['crteTime'], '%Y-%m-%d %H:%M:%S') if data['crteTime'] is not None else None
        lb_crterId = data['crterId']
        lb_crterName = data['crterName']
        lb_cvlservFlag = data['cvlservFlag']
        lb_cvlservLv = data['cvlservLv']
        lb_cvlservPay = data['cvlservPay']
        lb_dedcHospLv = data['dedcHospLv']
        lb_dedcStd = data['dedcStd']
        lb_diseName = data['diseName']
        lb_diseNo = data['diseNo']
        lb_drugSum = data['drugSum']
        lb_econType = data['econType']
        lb_empMgtType = data['empMgtType']
        lb_empName = data['empName']
        lb_empNo = data['empNo']
        lb_empType = data['empType']
        lb_endRow = data['endRow']
        lb_enddate = DatetimeUtil.msstamp2str(data['enddate'], '%Y-%m-%d %H:%M:%S') if data['enddate'] is not None else None
        lb_firstPage = data['firstPage']
        lb_fixBlngAdmdvs = data['fixBlngAdmdvs']
        lb_fixmedinsCode = data['fixmedinsCode']
        lb_fixmedinsName = data['fixmedinsName']
        lb_flxempeFlag = data['flxempeFlag']
        lb_fulamtOwnpayAmt = data['fulamtOwnpayAmt']
        lb_fundPaySumamt = data['fundPaySumamt']
        lb_gend = data['gend']
        lb_hiAgreSumfee = data['hiAgreSumfee']
        lb_hifdmPay = data['hifdmPay']
        lb_hifesPay = data['hifesPay']
        lb_hifmiPay = data['hifmiPay']
        lb_hifobPay = data['hifobPay']
        lb_hifpPay = data['hifpPay']
        lb_hospLv = data['hospLv']
        lb_initSetlId = data['initSetlId']
        lb_inscpAmt = data['inscpAmt']
        lb_insuAdmdvs = data['insuAdmdvs']
        lb_insutype = data['insutype']
        lb_inventoryFlag = data['inventoryFlag']
        lb_invono = data['invono']
        lb_lastPage = data['lastPage']
        lb_lmtpricHospLv = data['lmtpricHospLv']
        lb_mafPay = data['mafPay']
        lb_manlReimRea = data['manlReimRea']
        lb_mcsSum = data['mcsSum']
        lb_mdtrtCertNo = data['mdtrtCertNo']
        lb_mdtrtCertType = data['mdtrtCertType']
        lb_mdtrtId = data['mdtrtId']
        lb_medType = data['medType']
        lb_medfeeSumamt = data['medfeeSumamt']
        lb_medinsSetlId = data['medinsSetlId']
        lb_medinsStmtFlag = data['medinsStmtFlag']
        lb_memo = data['memo']
        lb_naty = data['naty']
        lb_nextPage = data['nextPage']
        lb_nwbFlag = data['nwbFlag']
        lb_optTime = DatetimeUtil.msstamp2str(data['optTime'], '%Y-%m-%d %H:%M:%S') if data['optTime'] is not None else None
        lb_opterId = data['opterId']
        lb_opterName = data['opterName']
        lb_optinsNo = data['optinsNo']
        lb_orderField = data['orderField']
        lb_orderType = data['orderType']
        lb_othfundPay = data['othfundPay']
        lb_overlmtSelfpay = data['overlmtSelfpay']
        lb_ownpayHospPart = data['ownpayHospPart']
        lb_pageNum = data['pageNum']
        lb_pageSize = data['pageSize']
        lb_pages = data['pages']
        lb_payLoc = data['payLoc']
        lb_poolPropSelfpay = data['poolPropSelfpay']
        lb_poolareaNo = data['poolareaNo']
        lb_prePage = data['prePage']
        lb_preselfpayAmt = data['preselfpayAmt']
        lb_projectSum = data['projectSum']
        lb_psnCertType = data['psnCertType']
        lb_psnInsuRltsId = data['psnInsuRltsId']
        lb_psnName = data['psnName']
        lb_psnNo = data['psnNo']
        lb_psnPay = data['psnPay']
        lb_psnSetlway = data['psnSetlway']
        lb_psnType = data['psnType']
        lb_recordCounts = data['recordCounts']
        lb_refdSetlFlag = data['refdSetlFlag']
        lb_rid = data['rid']
        lb_setlCashpayWay = data['setlCashpayWay']
        lb_setlId = data['setlId']
        lb_setlTime = DatetimeUtil.msstamp2str(data['setlTime'], '%Y-%m-%d %H:%M:%S') if data['setlTime'] is not None else None
        lb_setlType = data['setlType']
        lb_size = data['size']
        lb_spPsnType = data['spPsnType']
        lb_spPsnTypeLv = data['spPsnTypeLv']
        lb_startRow = data['startRow']
        lb_updtTime = DatetimeUtil.msstamp2str(data['updtTime'], '%Y-%m-%d %H:%M:%S') if data['updtTime'] is not None else None
        lb_valiFlag = data['valiFlag']
        lb_year = data['year']

        params = {
            "id": uuid.uuid4().hex,
            "grab_task_id": ToolConfig.task_id,
            "jigou_id": lb_fixmedinsCode,
            "acctMulaidPay": lb_acctMulaidPay,
            "acctPay": lb_acctPay,
            "actPayDedc": lb_actPayDedc,
            "afilIndu": lb_afilIndu,
            "afilRlts": lb_afilRlts,
            "age": lb_age,
            "bSetlId": lb_bSetlId,
            "balc": lb_balc,
            "begndate": lb_begndate,
            "brdy": lb_brdy,
            "calIptCnt": lb_calIptCnt,
            "cashPayamt": lb_cashPayamt,
            "certno": lb_certno,
            "clctGrde": lb_clctGrde,
            "clrAppyEvtId": lb_clrAppyEvtId,
            "clrOptins": lb_clrOptins,
            "clrType": lb_clrType,
            "clrWay": lb_clrWay,
            "crtDedc": lb_crtDedc,
            "crteOptinsNo": lb_crteOptinsNo,
            "crteTime": lb_crteTime,
            "crterId": lb_crterId,
            "crterName": lb_crterName,
            "cvlservFlag": lb_cvlservFlag,
            "cvlservLv": lb_cvlservLv,
            "cvlservPay": lb_cvlservPay,
            "dedcHospLv": lb_dedcHospLv,
            "dedcStd": lb_dedcStd,
            "diseName": lb_diseName,
            "diseNo": lb_diseNo,
            "drugSum": lb_drugSum,
            "econType": lb_econType,
            "empMgtType": lb_empMgtType,
            "empName": lb_empName,
            "empNo": lb_empNo,
            "empType": lb_empType,
            "endRow": lb_endRow,
            "enddate": lb_enddate,
            "firstPage": lb_firstPage,
            "fixBlngAdmdvs": lb_fixBlngAdmdvs,
            "fixmedinsCode": lb_fixmedinsCode,
            "fixmedinsName": lb_fixmedinsName,
            "flxempeFlag": lb_flxempeFlag,
            "fulamtOwnpayAmt": lb_fulamtOwnpayAmt,
            "fundPaySumamt": lb_fundPaySumamt,
            "gend": lb_gend,
            "hiAgreSumfee": lb_hiAgreSumfee,
            "hifdmPay": lb_hifdmPay,
            "hifesPay": lb_hifesPay,
            "hifmiPay": lb_hifmiPay,
            "hifobPay": lb_hifobPay,
            "hifpPay": lb_hifpPay,
            "hospLv": lb_hospLv,
            "initSetlId": lb_initSetlId,
            "inscpAmt": lb_inscpAmt,
            "insuAdmdvs": lb_insuAdmdvs,
            "insutype": lb_insutype,
            "inventoryFlag": lb_inventoryFlag,
            "invono": lb_invono,
            "lastPage": lb_lastPage,
            "lmtpricHospLv": lb_lmtpricHospLv,
            "mafPay": lb_mafPay,
            "manlReimRea": lb_manlReimRea,
            "mcsSum": lb_mcsSum,
            "mdtrtCertNo": lb_mdtrtCertNo,
            "mdtrtCertType": lb_mdtrtCertType,
            "mdtrtId": lb_mdtrtId,
            "medType": lb_medType,
            "medfeeSumamt": lb_medfeeSumamt,
            "medinsSetlId": lb_medinsSetlId,
            "medinsStmtFlag": lb_medinsStmtFlag,
            "memo": lb_memo,
            "naty": lb_naty,
            "nextPage": lb_nextPage,
            "nwbFlag": lb_nwbFlag,
            "optTime": lb_optTime,
            "opterId": lb_opterId,
            "opterName": lb_opterName,
            "optinsNo": lb_optinsNo,
            "orderField": lb_orderField,
            "orderType": lb_orderType,
            "othfundPay": lb_othfundPay,
            "overlmtSelfpay": lb_overlmtSelfpay,
            "ownpayHospPart": lb_ownpayHospPart,
            "pageNum": lb_pageNum,
            "pageSize": lb_pageSize,
            "pages": lb_pages,
            "payLoc": lb_payLoc,
            "poolPropSelfpay": lb_poolPropSelfpay,
            "poolareaNo": lb_poolareaNo,
            "prePage": lb_prePage,
            "preselfpayAmt": lb_preselfpayAmt,
            "projectSum": lb_projectSum,
            "psnCertType": lb_psnCertType,
            "psnInsuRltsId": lb_psnInsuRltsId,
            "psnName": lb_psnName,
            "psnNo": lb_psnNo,
            "psnPay": lb_psnPay,
            "psnSetlway": lb_psnSetlway,
            "psnType": lb_psnType,
            "recordCounts": lb_recordCounts,
            "refdSetlFlag": lb_refdSetlFlag,
            "rid": lb_rid,
            "setlCashpayWay": lb_setlCashpayWay,
            "setlId": lb_setlId,
            "setlTime": lb_setlTime,
            "setlType": lb_setlType,
            "size": lb_size,
            "spPsnType": lb_spPsnType,
            "spPsnTypeLv": lb_spPsnTypeLv,
            "startRow": lb_startRow,
            "updtTime": lb_updtTime,
            "valiFlag": lb_valiFlag,
            "year": lb_year
        }

        sql6 = """
        INSERT INTO t_tmp_ybtool1_bendi_mingxi (
            id, jigou_id, grab_task_id, lb_acctMulaidPay, lb_acctPay, lb_actPayDedc, lb_afilIndu, lb_afilRlts, lb_age,
            lb_bSetlId, lb_balc, lb_begndate, lb_brdy, lb_calIptCnt, lb_cashPayamt, lb_certno, lb_clctGrde, lb_clrAppyEvtId,
            lb_clrOptins, lb_clrType, lb_clrWay, lb_crtDedc, lb_crteOptinsNo, lb_crteTime, lb_crterId, lb_crterName, lb_cvlservFlag,
            lb_cvlservLv, lb_cvlservPay, lb_dedcHospLv, lb_dedcStd, lb_diseName, lb_diseNo, lb_drugSum, lb_econType, lb_empMgtType,
            lb_empName, lb_empNo, lb_empType, lb_endRow, lb_enddate, lb_firstPage, lb_fixBlngAdmdvs, lb_fixmedinsCode, lb_fixmedinsName,
            lb_flxempeFlag, lb_fulamtOwnpayAmt, lb_fundPaySumamt, lb_gend, lb_hiAgreSumfee, lb_hifdmPay, lb_hifesPay, lb_hifmiPay,
            lb_hifobPay, lb_hifpPay, lb_hospLv, lb_initSetlId, lb_inscpAmt, lb_insuAdmdvs, lb_insutype, lb_inventoryFlag, lb_invono,
            lb_lastPage, lb_lmtpricHospLv, lb_mafPay, lb_manlReimRea, lb_mcsSum, lb_mdtrtCertNo, lb_mdtrtCertType, lb_mdtrtId,
            lb_medType, lb_medfeeSumamt, lb_medinsSetlId, lb_medinsStmtFlag, lb_memo, lb_naty, lb_nextPage, lb_nwbFlag, lb_optTime,
            lb_opterId, lb_opterName, lb_optinsNo, lb_orderField, lb_orderType, lb_othfundPay, lb_overlmtSelfpay, lb_ownpayHospPart,
            lb_pageNum, lb_pageSize, lb_pages, lb_payLoc, lb_poolPropSelfpay, lb_poolareaNo, lb_prePage, lb_preselfpayAmt, lb_projectSum,
            lb_psnCertType, lb_psnInsuRltsId, lb_psnName, lb_psnNo, lb_psnPay, lb_psnSetlway, lb_psnType, lb_recordCounts, lb_refdSetlFlag,
            lb_rid, lb_setlCashpayWay, lb_setlId, lb_setlTime, lb_setlType, lb_size, lb_spPsnType, lb_spPsnTypeLv, lb_startRow, lb_updtTime,
            lb_valiFlag, lb_year
        ) VALUES (
            %(id)s, %(jigou_id)s, %(grab_task_id)s, %(acctMulaidPay)s, %(acctPay)s, %(actPayDedc)s, %(afilIndu)s, 
            %(afilRlts)s, %(age)s, %(bSetlId)s, %(balc)s, %(begndate)s, %(brdy)s, %(calIptCnt)s, %(cashPayamt)s, 
            %(certno)s, %(clctGrde)s, %(clrAppyEvtId)s, %(clrOptins)s, %(clrType)s, %(clrWay)s, %(crtDedc)s, 
            %(crteOptinsNo)s, %(crteTime)s, %(crterId)s, %(crterName)s, %(cvlservFlag)s, %(cvlservLv)s, %(cvlservPay)s, 
            %(dedcHospLv)s, %(dedcStd)s, %(diseName)s, %(diseNo)s, %(drugSum)s, %(econType)s, %(empMgtType)s, 
            %(empName)s, %(empNo)s, %(empType)s, %(endRow)s, %(enddate)s, %(firstPage)s, %(fixBlngAdmdvs)s, 
            %(fixmedinsCode)s, %(fixmedinsName)s, %(flxempeFlag)s, %(fulamtOwnpayAmt)s, %(fundPaySumamt)s, %(gend)s, 
            %(hiAgreSumfee)s, %(hifdmPay)s, %(hifesPay)s, %(hifmiPay)s, %(hifobPay)s, %(hifpPay)s, %(hospLv)s, 
            %(initSetlId)s, %(inscpAmt)s, %(insuAdmdvs)s, %(insutype)s, %(inventoryFlag)s, %(invono)s, %(lastPage)s, 
            %(lmtpricHospLv)s, %(mafPay)s, %(manlReimRea)s, %(mcsSum)s, %(mdtrtCertNo)s, %(mdtrtCertType)s, %(mdtrtId)s, 
            %(medType)s, %(medfeeSumamt)s, %(medinsSetlId)s, %(medinsStmtFlag)s, %(memo)s, %(naty)s, %(nextPage)s, 
            %(nwbFlag)s, %(optTime)s, %(opterId)s, %(opterName)s, %(optinsNo)s, %(orderField)s, %(orderType)s, 
            %(othfundPay)s, %(overlmtSelfpay)s, %(ownpayHospPart)s, %(pageNum)s, %(pageSize)s, %(pages)s, %(payLoc)s, 
            %(poolPropSelfpay)s, %(poolareaNo)s, %(prePage)s, %(preselfpayAmt)s, %(projectSum)s, %(psnCertType)s, 
            %(psnInsuRltsId)s, %(psnName)s, %(psnNo)s, %(psnPay)s, %(psnSetlway)s, %(psnType)s, %(recordCounts)s, 
            %(refdSetlFlag)s, %(rid)s, %(setlCashpayWay)s, %(setlId)s, %(setlTime)s, %(setlType)s, %(size)s, %(spPsnType)s, 
            %(spPsnTypeLv)s, %(startRow)s, %(updtTime)s, %(valiFlag)s, %(year)s
        )
        ON DUPLICATE KEY UPDATE 
        lb_acctMulaidPay = VALUES(lb_acctMulaidPay), lb_acctPay = VALUES(lb_acctPay), lb_actPayDedc = VALUES(lb_actPayDedc),
        lb_afilIndu = VALUES(lb_afilIndu), lb_afilRlts = VALUES(lb_afilRlts), lb_age = VALUES(lb_age), lb_bSetlId = VALUES(lb_bSetlId),
        lb_balc = VALUES(lb_balc), lb_begndate = VALUES(lb_begndate), lb_brdy = VALUES(lb_brdy), lb_calIptCnt = VALUES(lb_calIptCnt),
        lb_cashPayamt = VALUES(lb_cashPayamt), lb_certno = VALUES(lb_certno), lb_clctGrde = VALUES(lb_clctGrde), lb_clrAppyEvtId = VALUES(lb_clrAppyEvtId),
        lb_clrOptins = VALUES(lb_clrOptins), lb_clrType = VALUES(lb_clrType), lb_clrWay = VALUES(lb_clrWay), lb_crtDedc = VALUES(lb_crtDedc),
        lb_crteOptinsNo = VALUES(lb_crteOptinsNo), lb_crteTime = VALUES(lb_crteTime), lb_crterId = VALUES(lb_crterId), lb_crterName = VALUES(lb_crterName),
        lb_cvlservFlag = VALUES(lb_cvlservFlag), lb_cvlservLv = VALUES(lb_cvlservLv), lb_cvlservPay = VALUES(lb_cvlservPay), lb_dedcHospLv = VALUES(lb_dedcHospLv),
        lb_dedcStd = VALUES(lb_dedcStd), lb_diseName = VALUES(lb_diseName), lb_diseNo = VALUES(lb_diseNo), lb_drugSum = VALUES(lb_drugSum),
        lb_econType = VALUES(lb_econType), lb_empMgtType = VALUES(lb_empMgtType), lb_empName = VALUES(lb_empName), lb_empNo = VALUES(lb_empNo),
        lb_empType = VALUES(lb_empType), lb_endRow = VALUES(lb_endRow), lb_enddate = VALUES(lb_enddate), lb_firstPage = VALUES(lb_firstPage),
        lb_fixBlngAdmdvs = VALUES(lb_fixBlngAdmdvs), lb_fixmedinsCode = VALUES(lb_fixmedinsCode), lb_fixmedinsName = VALUES(lb_fixmedinsName),
        lb_flxempeFlag = VALUES(lb_flxempeFlag), lb_fulamtOwnpayAmt = VALUES(lb_fulamtOwnpayAmt), lb_fundPaySumamt = VALUES(lb_fundPaySumamt),
        lb_gend = VALUES(lb_gend), lb_hiAgreSumfee = VALUES(lb_hiAgreSumfee), lb_hifdmPay = VALUES(lb_hifdmPay), lb_hifesPay = VALUES(lb_hifesPay),
        lb_hifmiPay = VALUES(lb_hifmiPay), lb_hifobPay = VALUES(lb_hifobPay), lb_hifpPay = VALUES(lb_hifpPay), lb_hospLv = VALUES(lb_hospLv),
        lb_initSetlId = VALUES(lb_initSetlId), lb_inscpAmt = VALUES(lb_inscpAmt), lb_insuAdmdvs = VALUES(lb_insuAdmdvs), lb_insutype = VALUES(lb_insutype),
        lb_inventoryFlag = VALUES(lb_inventoryFlag), lb_invono = VALUES(lb_invono), lb_lastPage = VALUES(lb_lastPage), lb_lmtpricHospLv = VALUES(lb_lmtpricHospLv),
        lb_mafPay = VALUES(lb_mafPay), lb_manlReimRea = VALUES(lb_manlReimRea), lb_mcsSum = VALUES(lb_mcsSum), lb_mdtrtCertNo = VALUES(lb_mdtrtCertNo),
        lb_mdtrtCertType = VALUES(lb_mdtrtCertType), lb_mdtrtId = VALUES(lb_mdtrtId), lb_medType = VALUES(lb_medType), lb_medfeeSumamt = VALUES(lb_medfeeSumamt),
        lb_medinsSetlId = VALUES(lb_medinsSetlId), lb_medinsStmtFlag = VALUES(lb_medinsStmtFlag), lb_memo = VALUES(lb_memo), lb_naty = VALUES(lb_naty),
        lb_nextPage = VALUES(lb_nextPage), lb_nwbFlag = VALUES(lb_nwbFlag), lb_optTime = VALUES(lb_optTime), lb_opterId = VALUES(lb_opterId),
        lb_opterName = VALUES(lb_opterName), lb_optinsNo = VALUES(lb_optinsNo), lb_orderField = VALUES(lb_orderField), lb_orderType = VALUES(lb_orderType),
        lb_othfundPay = VALUES(lb_othfundPay), lb_overlmtSelfpay = VALUES(lb_overlmtSelfpay), lb_ownpayHospPart = VALUES(lb_ownpayHospPart),
        lb_pageNum = VALUES(lb_pageNum), lb_pageSize = VALUES(lb_pageSize), lb_pages = VALUES(lb_pages), lb_payLoc = VALUES(lb_payLoc),
        lb_poolPropSelfpay = VALUES(lb_poolPropSelfpay), lb_poolareaNo = VALUES(lb_poolareaNo), lb_prePage = VALUES(lb_prePage), lb_preselfpayAmt = VALUES(lb_preselfpayAmt),
        lb_projectSum = VALUES(lb_projectSum), lb_psnCertType = VALUES(lb_psnCertType), lb_psnInsuRltsId = VALUES(lb_psnInsuRltsId),
        lb_psnName = VALUES(lb_psnName), lb_psnNo = VALUES(lb_psnNo), lb_psnPay = VALUES(lb_psnPay), lb_psnSetlway = VALUES(lb_psnSetlway),
        lb_psnType = VALUES(lb_psnType), lb_recordCounts = VALUES(lb_recordCounts), lb_refdSetlFlag = VALUES(lb_refdSetlFlag),
        lb_rid = VALUES(lb_rid), lb_setlCashpayWay = VALUES(lb_setlCashpayWay), lb_setlId = VALUES(lb_setlId), lb_setlTime = VALUES(lb_setlTime),
        lb_setlType = VALUES(lb_setlType), lb_size = VALUES(lb_size), lb_spPsnType = VALUES(lb_spPsnType), lb_spPsnTypeLv = VALUES(lb_spPsnTypeLv),
        lb_startRow = VALUES(lb_startRow), lb_updtTime = VALUES(lb_updtTime), lb_valiFlag = VALUES(lb_valiFlag), lb_year = VALUES(lb_year)
        """

        SqlHelper.insert(sql6, params)





# 保存本地结算明细采集的数据到最终的结算明细表中
def _save_bendi_to_ybtool1_jiesuan_mingxi(arrData):
    for data18 in arrData:
        psnNo8=data18['psnNo']
        stSetlTime=data18['setlTime']
        setlTime8 = DatetimeUtil.msstamp2str(stSetlTime, '%Y-%m-%d %H:%M:%S')

        setlId8= data18['setlId']
        psnName8=data18['psnName']
        insuAdmdvsName8 = data18['insuAdmdvs']
        insutype8=data18['insutype']
        insutypeName8=''
        medType8=data18['medType']
        fixmedinsCode8=data18['fixmedinsCode']

        fixmedinsName8=data18['fixmedinsName']

        cardNo8 = data18['certno']
        age8=data18['age']
        gend8=data18['gend']
        diseName8=data18['diseName']
        diseNo8=data18['diseNo']
        begndate8= DatetimeUtil.msstamp2str(data18['begndate'],'%Y-%m-%d %H:%M:%S') if data18['begndate'] is not None else None
        enddate8= DatetimeUtil.msstamp2str(data18['enddate'],'%Y-%m-%d %H:%M:%S') if data18['enddate'] is not None else None
        
        hiAgreSumfee8=data18['hiAgreSumfee']
        inscpAmt8=data18['inscpAmt']
        crtDedc8=data18['crtDedc']
        fundPaySumamt8=data18['fundPaySumamt']

        hifpPay8=data18['hifpPay']
        hifmiPay8=data18['hifmiPay']
        hifobPay8=data18['hifobPay']
        mafPay8=data18['mafPay']
        cvlservPay8=data18['cvlservPay']
        othPay8=data18['othfundPay']
        hifesPay8=data18['hifesPay']
        fulamtOwnpayAmt8=data18['fulamtOwnpayAmt']
        psnPay8=data18['psnPay']
        acctPay8=data18['acctPay']

        acctMulaidPay8=data18['acctMulaidPay']
        cashPayamt8=data18['cashPayamt']
        poolPropSelfpay8=data18['poolPropSelfpay']
        refdSetlFlag8=data18['refdSetlFlag']


        params={ "id":uuid.uuid4().hex, "setl_trans_numb": setlId8, "psnNo":psnNo8,
                "psnName": psnName8, "setlTime":setlTime8,\
                "insuAdmdvs":'',"insuAdmdvsName":insuAdmdvsName8,\
                "insutype":insutype8, "insutypeName":insutypeName8, \
                "medType":medType8,"medTypeName":'',"fixmedinsCode":fixmedinsCode8,\
                "fixmedinsName":fixmedinsName8, "cardNo":cardNo8,\
            "age":age8,"gend":gend8,"diseName":diseName8,"diseNo":diseNo8,\
            "begndate":begndate8,"enddate":enddate8,\
            
            "hiAgreSumfee":hiAgreSumfee8,"inscpAmt":inscpAmt8,"crtDedc":crtDedc8,\
            "fundPaySumamt":fundPaySumamt8,"hifpPay":hifpPay8,"hifmiPay":hifmiPay8,"hifobPay":hifobPay8,\
            "mafPay":mafPay8,"cvlservPay":cvlservPay8,"othPay":othPay8,\
            "hifesPay":hifesPay8,"fulamtOwnpayAmt":fulamtOwnpayAmt8,"psnPay":psnPay8,\
            "acctPay":acctPay8,"acctMulaidPay":acctMulaidPay8,"cashPayamt":cashPayamt8,\
            "poolPropSelfpay":poolPropSelfpay8,"refdSetlFlag":refdSetlFlag8   }
        
        _updateJiesuanMingxiParam(params)

        _save_jiesuan_mingxi(psnNo8, setlTime8, params)

# 定点机构登录
def _ld_login(loginCode,loginPwd):
    cnt1 = 0
    while True:
        cnt1 +=1

        # 1 登录，获取session
        drv1=ToolConfig.chromeDriver

        if cnt1>5:
            logging.warn('尝试了5次，登录依旧失败。')

            ToolConfig.yb2siteSession=None
            ToolConfig.yb2siteToken=None

            return

        drv1.get(ToolConfig.yb2siteUrl)

        # 检测页面表填 #tab-code
        elem1= SeleniumUtil.findElement(driver=drv1, waitTime=3, value="#tab-code")
        # 验证当前是登录 页面
        if elem1 is None:

            cookies = drv1.get_cookies()
            session1 =None
            token1=None
            for cookie in cookies:  
                if cookie['name'] =='SESSION':
                    session1 = cookie['value']
                
                if cookie['name'] =='XSRF-TOKEN':
                    token1 = cookie['value']

            ToolConfig.yb2siteSession=session1
            ToolConfig.yb2siteToken=token1
            return


        else:
            elemUsername=SeleniumUtil.findElement( driver=drv1, value="input[name='username']")
            elemUserpwd=SeleniumUtil.findElement( driver=drv1, value="input[name='password']")
            elemInputValidateCode=SeleniumUtil.findElement( 
                driver=drv1, value="input[name='verificationCode']"
                ,waitTime=0)

            strVerificateCode=_getldSiteLoginVerificateCode(driver =drv1)

            if elemUsername is not None:
                elemUsername.send_keys(loginCode)
            else:
                logging.warn(f"未能找到用户名输入文本框")
                time.sleep(5)
                continue
            
            if elemUserpwd is not None:
                elemUserpwd.send_keys(loginPwd)
            else:
                logging.warn(f"未能找到用户密码输入文本框")
                time.sleep(5)
                continue

            # 输入验证码
            if elemInputValidateCode is not None:
                elemInputValidateCode.clear()
                elemInputValidateCode.send_keys(strVerificateCode)
            else:
                logging.warn(f"未能找到验证码输入文本框")
                time.sleep(5)
                continue
            
            
            elemBtnLogin=SeleniumUtil.findElement( driver=drv1, value="button.login-btn")
            if elemBtnLogin is not None:
                elemBtnLogin.click()  

            time.sleep(5)

        
def _ld_logout():

    drv1 = ToolConfig.chromeDriver
    elemUserContainer = SeleniumUtil.findElement(driver=drv1, value="div.user-info-container")
    if elemUserContainer is not None:
        actions = ActionChains(drv1)  
        actions.move_to_element(elemUserContainer).perform()  

    elemBtnLogout=SeleniumUtil.findElement(driver=drv1, value="div.logout-item")
    if elemBtnLogout is not None:
        actions = ActionChains(drv1)  
        actions.click(elemBtnLogout).perform()

    ToolConfig.yb2siteSession =None
    ToolConfig.yb2siteToken=None

    drv1.delete_all_cookies()
    drv1.refresh()

    return 0

# 获取ld 登录页面的验证码
def _getldSiteLoginVerificateCode(driver):

    elemImgVerificateCode=SeleniumUtil.findElement(driver=driver, value="div.verificationCodeImage")

    if elemImgVerificateCode is  None:
        return ''

    strBase64=_getBase64Str4ldLogin(elemImg=elemImgVerificateCode)
    imgBytes=ImageUtil.base64ToBytes(strbase64=strBase64)

    # bytes 中获取验证码的文字 
    str1=OcrUtil.textFromBytes(imgBytes)
    return str1

def _getBase64Str4ldLogin(elemImg):
    style_attr = elemImg.get_attribute('style')  

    start_index = style_attr.find('base64,') + len('base64,')  
    end_index = style_attr.find(';', start_index)  
    if start_index != -1 and end_index != -1:  
        base64_img_data = style_attr[start_index:end_index]  
        return base64_img_data
    else:  
        print("Failed to find base64 data in the style attribute.")  
        return ''

# 保存异地结算明细数据到 t_ybtool1_jiesuan_mingxi 表中
def step6_save_yidi_jiesuan_mingxi():
    
    logging.info("== 开始将异地结算明细数据保存到 t_ybtool1_jiesuan_mingxi 表")

    # 获取异地就医结算明细数据的总数
    sql9='select count(1) as cnt1 from t_tmp_ybtool1_yidi_mingxi_excel where grab_task_id=%s'
    param9 =  ( ToolConfig.task_id, )
    res9=SqlHelper.fetch_one(sql=sql9, args=param9 )
    cnt9=res9['cnt1']

    # 分页读取异地就医结算明细的数据
    pageSize=100
    for i9 in range(0, cnt9,pageSize):

        logging.info(f"==== 执行异地结算明细数据保存，{i9}/{cnt9}")

        startIndex9=i9

        # 循环读取若干条 异地结算明细
        sql8='select * from t_tmp_ybtool1_yidi_mingxi_excel where grab_task_id = %s order by id desc limit %s,%s'
        param8=(ToolConfig.task_id, startIndex9,pageSize, )
        res8 = SqlHelper.fetch_all(sql8, param8)

        ## 循环每条结算明细，读取结算人
        for data8 in res8:
            setlTransNumb=data8['xlsx_col41']
            psnNo8=data8['xlsx_col43']
            psnName8=data8['xlsx_col6']
            insuAdmdvsName8=data8['xlsx_col2']
            insutypeName8=data8['xlsx_col7']
            medTypeName8=data8['xlsx_col9']
            setlTime8=data8['xlsx_col32']
            fixmedinsCode8=data8['xlsx_col33']
            fixmedinsName8=data8['xlsx_col34']
            cardNo8=data8['xlsx_col5']
            age8=IdCardUtil.calc_age_from_id(cardNo8)
            gend8=IdCardUtil.get_gender_from_id(cardNo8)
            diseNo8=data8['xlsx_col47']
            diseName8=data8['xlsx_col48']
            begndate8=data8['xlsx_col36']
            enddate8=data8['xlsx_col37']
            hiAgreSumfee8=data8['xlsx_col10']
            
            inscpAmt8=data8['xlsx_col22']
            crtDedc8=data8['xlsx_col21']
            fundPaySumamt8=data8['xlsx_col11']
            hifpPay8=data8['xlsx_col12']
            hifmiPay8=data8['xlsx_col16']
            hifobPay8=0
            mafPay8=data8['xlsx_col17']
            cvlservPay8=data8['xlsx_col13']
            othPay8=data8['xlsx_col18']
            hifesPay8=data8['xlsx_col14']
            fulamtOwnpayAmt8=data8['xlsx_col26']
            psnPay8=None
            acctPay8=data8['xlsx_col19']
            acctMulaidPay8=None
            cashPayamt8=data8['xlsx_col20']
            poolPropSelfpay8=None
            refdSetlFlag8=data8['xlsx_col39']


            
            param6 = {"id":uuid.uuid4().hex, "setl_trans_numb": setlTransNumb, "psnNo":psnNo8,
                      "psnName": psnName8, "setlTime":setlTime8,\
                    "insuAdmdvs":'',"insutype":'', \
                    "insuAdmdvsName":insuAdmdvsName8,"insutypeName":insutypeName8, \
                    "medType":'',"medTypeName":medTypeName8,"fixmedinsCode":fixmedinsCode8,\
                    "fixmedinsName":fixmedinsName8, "cardNo": cardNo8 ,\
                "age":age8,"gend":gend8,"diseName":diseName8,"diseNo":diseNo8,\
                "begndate":begndate8,"enddate":enddate8,\
                
                "hiAgreSumfee":hiAgreSumfee8,"inscpAmt":inscpAmt8,"crtDedc":crtDedc8,\
                "fundPaySumamt":fundPaySumamt8,"hifpPay":hifpPay8,"hifmiPay":hifmiPay8,"hifobPay":hifobPay8,\
                "mafPay":mafPay8,"cvlservPay":cvlservPay8,"othPay":othPay8,\
                "hifesPay":hifesPay8,"fulamtOwnpayAmt":fulamtOwnpayAmt8,"psnPay":psnPay8,\
                "acctPay":acctPay8,"acctMulaidPay":acctMulaidPay8,"cashPayamt":cashPayamt8,\
                "poolPropSelfpay":poolPropSelfpay8,"refdSetlFlag":refdSetlFlag8                
            }

            _updateJiesuanMingxiParam(param6)
            
            _save_jiesuan_mingxi(psnNo8, setlTime8, param6)

    logging.info(f"**** 执行异地结算明细数据保存完成")

    return 0

def _updateJiesuanMingxiParam(param):
    
    # 设置清河县编号
    if param['insuAdmdvs'] == '' and '清河县' in param['insuAdmdvsName']:
        param['insuAdmdvs'] = '130534'
    
    elif param['insuAdmdvs'] != '' and param['insuAdmdvsName']=='':
        param['insuAdmdvsName'] ='清河县'

    if param['refdSetlFlag'] =='有效':
        param['refdSetlFlag'] = '0'
    elif param['refdSetlFlag'] =='无效':
        param['refdSetlFlag'] = '1'

    # 整理 省、市、县区
    if 'fixmedinsCode' in param and len(param['fixmedinsCode']) > 6:
        fixmedinsCode=param['fixmedinsCode']
        if fixmedinsCode[0]== 'H' or fixmedinsCode[0]== 'P':
            fixmedinsCode = fixmedinsCode[1:]
        shengCode = fixmedinsCode[0:2]
        shiCode=fixmedinsCode[0:4]
        xianCode=fixmedinsCode[0:6]

        shengName = ToolConfig.shengJson[shengCode] if shengCode in ToolConfig.shengJson else '' 
        shiName = ToolConfig.shiJson[shiCode]  if shiCode in ToolConfig.shiJson else '' 
        xianName = ToolConfig.xianJson[xianCode]   if xianCode in ToolConfig.xianJson else ''

        param['sheng_code']=shengCode
        param['shi_code'] = shiCode if shiCode is not None else shengCode
        param['xian_code'] = xianCode if xianCode is not None else shiCode

        param['sheng_name']=shengName
        param['shi_name'] = shiName if shiName is not None and shiName !='' else shengName
        param['xian_name'] = xianName if xianName is not None and xianName!='' else param['shi_name']
        
    # 如果 insutype 为空，根据 insutypeName 获取 insutype
    if param['insutype'] == '' and param['insutypeName'] != '':
        if param['insutypeName'] in ToolConfig.insutype_reverse_mapping:
            param['insutype'] = ToolConfig.insutype_reverse_mapping[param['insutypeName']]
        else:
            print(f"未找到 insutypeName: {param['insutypeName']} 对应的 insutype")

    # 如果 insutypeName 为空，根据 insutype 获取 insutypeName
    elif param['insutype'] != '' and param['insutypeName'] == '':
        if param['insutype'] in ToolConfig.insutype_mapping:
            param['insutypeName'] = ToolConfig.insutype_mapping[param['insutype']]
        else:
            print(f"未找到 insutype: {param['insutype']} 对应的 insutypeName")

    

    # 如果 medType 为空，根据 medTypeName 获取 medType
    if param['medType'] == '' and param['medTypeName'] != '':
        if param['medTypeName'] in ToolConfig.medType_reverse_mapping:
            param['medType'] = ToolConfig.medType_reverse_mapping[param['medTypeName']]
        else:
            print(f"未找到 medTypeName: {param['medTypeName']} 对应的 medType")

    # 如果 medTypeName 为空，根据 medType 获取 medTypeName
    elif param['medType'] != '' and param['medTypeName'] == '':
        if param['medType'] in ToolConfig.medType_mapping:
            param['medTypeName'] = ToolConfig.medType_mapping[param['medType']]
        else:
            print(f"未找到 medType: {param['medType']} 对应的 medTypeName")

    


def _save_jiesuan_mingxi( psnNo,setlTime, params ):
    ## 移除已经存在的结算明细在 t_ybtool1_jiesuan_mingxi 表
    sql7 = 'select id,setlTime from t_ybtool1_jiesuan_mingxi where psnno=%s \
       and setltime BETWEEN DATE_SUB(%s, INTERVAL 5 SECOND) AND DATE_ADD(%s, INTERVAL 5 SECOND)   limit 1'
    param7=( psnNo,setlTime,setlTime,)
    res7= SqlHelper.fetch_one(sql7, param7)

    # 如果是新的结算明细的，则新增，否则就更新
    if res7 is None:
        sql6="insert into t_ybtool1_jiesuan_mingxi (id,setl_trans_numb,psnNo,psnName,setlTime,\
            sheng_code,sheng_name,shi_code,shi_name,xian_code,xian_name, \
            insuAdmdvs,insuAdmdvsName,insutype,insutypeName,medType,medTypeName,fixmedinsCode,fixmedinsName,\
            age,gend,diseName,diseNo,\
            begndate,enddate,hiAgreSumfee,inscpAmt,crtDedc,fundPaySumamt,hifpPay,hifmiPay,\
            hifobPay,mafPay,cvlservPay,othPay,hifesPay,fulamtOwnpayAmt,psnPay,acctPay,acctMulaidPay,\
            cashPayamt,poolPropSelfpay,refdSetlFlag) values \
            ( %(id)s,%(setl_trans_numb)s,%(psnNo)s,%(psnName)s,%(setlTime)s,\
            %(sheng_code)s,%(sheng_name)s,%(shi_code)s,%(shi_name)s,%(xian_code)s,%(xian_name)s, \
            %(insuAdmdvs)s,%(insuAdmdvsName)s,%(insutype)s,%(insutypeName)s,%(medType)s,%(medTypeName)s,%(fixmedinsCode)s,%(fixmedinsName)s,\
            %(age)s,%(gend)s,%(diseName)s,%(diseNo)s,\
            %(begndate)s,%(enddate)s,%(hiAgreSumfee)s,%(inscpAmt)s,%(crtDedc)s,\
            %(fundPaySumamt)s,%(hifpPay)s,%(hifmiPay)s,\
            %(hifobPay)s,%(mafPay)s,%(cvlservPay)s,%(othPay)s,%(hifesPay)s,%(fulamtOwnpayAmt)s,\
            %(psnPay)s,%(acctPay)s,%(acctMulaidPay)s,\
            %(cashPayamt)s,%(poolPropSelfpay)s,%(refdSetlFlag)s )"
        SqlHelper.insert(sql6, params)
    else:

        seltTime6 = res7['setlTime']
        params['seltTime'] = seltTime6

        arrFieldName = ['psnName','sheng_code','sheng_name','shi_code','shi_name','xian_code','xian_name',
                        'insuAdmdvs','insuAdmdvsName','insutype','insutypeName','medType','medTypeName',
                        'fixmedinsCode','fixmedinsName','age','gend','diseName','diseNo',
                        'begndate','enddate','hiAgreSumfee','inscpAmt','crtDedc','fundPaySumamt',
                        'hifpPay','hifmiPay', 'hifobPay','mafPay','cvlservPay','othPay','hifesPay',
                        'fulamtOwnpayAmt','psnPay','acctPay','acctMulaidPay','cashPayamt',
                        'poolPropSelfpay','refdSetlFlag']
        sql6='update t_ybtool1_jiesuan_mingxi set '
        
        for fieldName in arrFieldName:
            sql6+=f" {fieldName}=%({fieldName})s," if params[fieldName] is not None and params[fieldName] !='' and  params[fieldName] !=0 else ' '
        
        sql6=sql6[0: len(sql6)-1]

        sql6+=' where psnNo = %(psnNo)s and  setlTime = %(setlTime)s'
        SqlHelper.update(sql6, params)


# 比对本期结算总额、上期结算总额、本地明细和异地明细，然后找到有差异的人员，并采集这些人员的结算明细
def step12_grab_all_person_jiesuan():
    logging.info(f" === 开始比对本期结算总额、上期结算总额、本地明细和异地明细，然后找到有差异的人员，并采集这些人员的结算明细 ")
    # 比对本次任务采集的报表总额，本期与上期总额值不同的人员的psnNo列表
    sql9from= " t_tmp_ybtool1_all_excel2 where grab_task_id=%(taskId)s and ( benqi_money != shangqi_money or shangqi_money is null ) "
    
    if "allMingxiPsnNo" in  ToolConfig.TaskStatusJson and ToolConfig.TaskStatusJson['allMingxiPsnNo'] !='':
        sql9from += f" and psnNo > %(allMingxiPsnNo)s"
    

    sql9Count = f"SELECT count(1) as cnt1 FROM {sql9from}"

    param9 = {'taskId': ToolConfig.task_id, 'allMingxiPsnNo': ToolConfig.TaskStatusJson['allMingxiPsnNo'] }
    # 查询 本期结算总额 和上期结算总额，有差异的数量
    i1=time.time()
    res9Count = SqlHelper.fetch_one(sql9Count, param9 )
    i2=time.time()
    logging.info(f" === {i2-i1} 本期结算总额与上期结算总额的金额有差异的数量为： {res9Count['cnt1']}, 接下来将针对这些人员比对本地结算数据和异地结算数据，依旧存在差异的结算总额将从报表子系统采集明细数据。")
    
    # 如果数量为0，则不需要采集，直接返回
    if res9Count['cnt1'] ==0:
        return
    
    # 先登录 到 heb
    _heb_login()

    page9Size=10
    
    psnNoCntCaiji=0
    psnNoCntNoCaiji=0

    min1 = ToolConfig.configJson['grab_min_interval']
    max1 = ToolConfig.configJson['grab_max_interval']

    for pageIndex in range(0,res9Count['cnt1'], page9Size):
        sql9Limit= f"select psnNo, benqi_money,shangqi_money from  {sql9from} limit {pageIndex},{page9Size}"
        res9Limit = SqlHelper.fetch_all(sql9Limit, param9)

        for data9 in res9Limit:
            psnNo9 = data9['psnNo']
            benqiMoney9 = data9['benqi_money']
            shangqiMoney9 = data9['shangqi_money'] if data9['shangqi_money'] is not None else 0

            # 汇总 psnNo9 本期结算明细的总金额 ，即从 shangqi_end_time 开始 benqi_end_time 之间的金额
            sql8="select sum(hiAgreSumfee) as zmoney from t_ybtool1_jiesuan_mingxi where psnNo=%(psnNo)s \
                and setlTime > %(beginTime)s and setlTime < %(endTime)s  "
            param8 = {"psnNo": psnNo9, "beginTime": ToolConfig.dtShangqiEndTime,"endTime": ToolConfig.dtBenqiEndTime }
            res8 = SqlHelper.fetch_one(sql8, param8)

            zmoney = res8['zmoney'] if res8['zmoney'] is not None else 0
            
            # 比较 benqiMoney9 - shangqiMoney9 - zmoney ，如果不等于0，则采集明细
            bj7 = benqiMoney9 - shangqiMoney9 - zmoney

            result10 = 0
            if bj7 > 0:
                psnNoCntCaiji+=1

                result10 = _grab_all_person_jiesuan(psnNo9)

                # 采集成功 
                if result10>0:
                    tmsleep = random.randrange(min1,max1)/1000.0
                    time.sleep(tmsleep)

            else:
                psnNoCntNoCaiji+=1

            print(f"{"+++需要采集：" if bj7>0 else "---无需采集："} psnNo: {psnNo9}, 本期总额：{benqiMoney9}, 上期总额:{shangqiMoney9}, 结算明细表合计,采集前： {zmoney}， 本次采集： {result10}")
            
            ToolConfig.TaskStatusJson['allMingxiPsnNo'] = psnNo9
            updateTaskStatusInfo()

            if "maxGrabCount" in  ToolConfig.configJson and \
                ToolConfig.configJson['maxGrabCount'] >0 and \
                psnNoCntCaiji >= ToolConfig.configJson['maxGrabCount']:
                break

        if "maxGrabCount" in  ToolConfig.configJson and \
                ToolConfig.configJson['maxGrabCount'] >0 and \
                psnNoCntCaiji >= ToolConfig.configJson['maxGrabCount']:
            break

        logging.info(f"报表子系统采集进度 {psnNoCntCaiji+psnNoCntNoCaiji} / {res9Count['cnt1']}")


def step12_grab_all_person_jiesuan2():
    psnno9='13050085341178094'
    logging.info(f" === 开始比对本期结算总额、上期结算总额、本地明细和异地明细，然后找到有差异的人员，并采集这些人员的结算明细 ")
    # 比对本次任务采集的报表总额，本期与上期总额值不同的人员的psnNo列表
    sql9from= " t_tmp_ybtool1_all_excel2 where grab_task_id=%(taskId)s and ( benqi_money != shangqi_money or shangqi_money is null ) "
    
    sql9from += f" and psnNo = %(psnNo)s"
    

    sql9Count = f"SELECT count(1) as cnt1 FROM {sql9from}"

    param9 = {'taskId': ToolConfig.task_id, 'psnNo': psnno9 }
    # 查询 本期结算总额 和上期结算总额，有差异的数量
    i1=time.time()
    res9Count = SqlHelper.fetch_one(sql9Count, param9 )
    i2=time.time()
    logging.info(f" === {i2-i1} 本期结算总额与上期结算总额的金额有差异的数量为： {res9Count['cnt1']}, 接下来将针对这些人员比对本地结算数据和异地结算数据，依旧存在差异的结算总额将从报表子系统采集明细数据。")
    
    # 如果数量为0，则不需要采集，直接返回
    if res9Count['cnt1'] ==0:
        return
    
    # 先登录 到 heb
    _heb_login()

    page9Size=100
    
    psnNoCntCaiji=0
    psnNoCntNoCaiji=0

    min1 = ToolConfig.configJson['grab_min_interval']
    max1 = ToolConfig.configJson['grab_max_interval']

    for pageIndex in range(0,res9Count['cnt1'], page9Size):
        sql9Limit= f"select psnNo, benqi_money,shangqi_money from  {sql9from} limit {pageIndex},{page9Size}"
        res9Limit = SqlHelper.fetch_all(sql9Limit, param9)

        for data9 in res9Limit:
            psnNo9 = data9['psnNo']
            benqiMoney9 = data9['benqi_money']
            shangqiMoney9 = data9['shangqi_money'] if data9['shangqi_money'] is not None else 0

            # 汇总 psnNo9 本期结算明细的总金额 ，即从 shangqi_end_time 开始 benqi_end_time 之间的金额
            sql8="select sum(hiAgreSumfee) as zmoney from t_ybtool1_jiesuan_mingxi where psnNo=%(psnNo)s \
                and setlTime > %(beginTime)s and setlTime < %(endTime)s  "
            param8 = {"psnNo": psnNo9, "beginTime": ToolConfig.dtShangqiEndTime,"endTime": ToolConfig.dtBenqiEndTime }
            res8 = SqlHelper.fetch_one(sql8, param8)

            zmoney = res8['zmoney'] if res8['zmoney'] is not None else 0
            
            # 比较 benqiMoney9 - shangqiMoney9 - zmoney ，如果不等于0，则采集明细
            bj7 = benqiMoney9 - shangqiMoney9 - zmoney
            
            result10 = 0
            if bj7 > 0:
                psnNoCntCaiji+=1

                result10 = _grab_all_person_jiesuan(psnNo9)

                # 采集成功 
                if result10>0:
                    tmsleep = random.randrange(min1,max1)/1000.0
                    time.sleep(tmsleep)

            else:
                psnNoCntNoCaiji+=1

            print(f"{"+++需要采集：" if bj7>0 else "---无需采集："} psnNo: {psnNo9}, 本期总额：{benqiMoney9}, 上期总额:{shangqiMoney9}, 结算明细表合计,采集前： {zmoney}， 本次采集： {result10}")
            
            ToolConfig.TaskStatusJson['allMingxiPsnNo'] = psnNo9
            updateTaskStatusInfo()

            if "maxGrabCount" in  ToolConfig.configJson and \
                ToolConfig.configJson['maxGrabCount'] >0 and \
                psnNoCntCaiji >= ToolConfig.configJson['maxGrabCount']:
                break

        if "maxGrabCount" in  ToolConfig.configJson and \
                ToolConfig.configJson['maxGrabCount'] >0 and \
                psnNoCntCaiji >= ToolConfig.configJson['maxGrabCount']:
            break

        logging.info(f"报表子系统采集进度 {psnNoCntCaiji+psnNoCntNoCaiji} / {res9Count['cnt1']}")



def _heb_login():
    cnt1 = 0
    while True:
        cnt1 +=1

        # 1 登录，获取session
        drv1=ToolConfig.chromeDriver

        if cnt1>5:
            logging.warn('尝试了5次，登录依旧失败。')

            ToolConfig.yb1siteSession=None
            ToolConfig.yb1siteToken=None

            return

        drv1.get(ToolConfig.yb1siteUrl)

        # 检测页面表填 #tab-code
        elem1= SeleniumUtil.findElement(driver=drv1, waitTime=3, value="#tab-ca")
        # 验证当前是登录 页面
        if elem1 is None:

            cookies = drv1.get_cookies()
            session1 =None
            token1=None
            for cookie in cookies:  
                if cookie['name'] =='SESSION':
                    session1 = cookie['value']
                
                if cookie['name'] =='XSRF-TOKEN':
                    token1 = cookie['value']

            ToolConfig.yb1siteSession=session1
            ToolConfig.yb1siteToken=token1
            return


        else:
            elemButtonLogin=SeleniumUtil.findElement( driver=drv1, value="button.ca-login")
            if elemButtonLogin is not None:
                actions = ActionChains(drv1)
                actions.click(elemButtonLogin).perform()

                # 查实查找登录窗口中的人员列表
                elemLiUser=SeleniumUtil.findElement(driver=drv1, value="div.el-dialog__wrapper ul.userlist li.user div.username",innerText="fzb")
                
                if elemLiUser !=None:
                    actions = ActionChains(drv1)
                    actions.click(elemLiUser).perform()
                else:
                    print("没有找到 fzb 的按钮")

            else:
                logging.warn(f"未能找到CA登录按钮")

            time.sleep(5)

# 根据psnNo 采集上期结束到本期结束之间的结算明细
def _grab_all_person_jiesuan(psnNo):

    
    curl1 = "curl 'http://heb.hsip.gov.cn/mbs-extend/web/settInfo/querySetlDetail?t=#t1#' \
    -H 'Accept: application/json, text/plain, */*' \
    -H 'Accept-Language: zh-CN,zh;q=0.9,en;q=0.8,en-GB;q=0.7,en-US;q=0.6' \
    -H 'Connection: keep-alive' \
    -H 'Content-Type: application/json;charset=UTF-8' \
    -H 'Cookie: XSRF-TOKEN=#token#; SESSION=#session#' \
    -H 'Origin: http://heb.hsip.gov.cn' \
    -H 'Referer: http://heb.hsip.gov.cn/mbs-extend/ui/settleAccountsQuery.html' \
    -H 'User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/125.0.0.0 Safari/537.36 Edg/125.0.0.0' \
    -H 'X-XSRF-TOKEN: #token#' \
    --data-raw '{\"psnNo\":\"#psnNo#\",\"insutype\":\"390\",\"insuAdmdvs\":\"130534\",\
    \"begndate\":\"#begindate#\",\"enddate\":\"#enddate#\",\"pageNum\":1,\"pageSize\":50}' \
    --insecure"

    # 开始抓取所有人员的结算明细 
    strBeginTime=DatetimeUtil.dt2str( ToolConfig.dtShangqiEndTime,"%Y%m%d")
    strEndTime=DatetimeUtil.dt2str( ToolConfig.dtBenqiEndTime,"%Y%m%d")
    
    curl2=curl1.replace('#token#',ToolConfig.yb1siteToken).replace('#session#',ToolConfig.yb1siteSession)\
    .replace('#psnNo#',psnNo).replace('#begindate#',strBeginTime).replace('#enddate#',strEndTime) \
    .replace("#t1", str(time.time()))

    requestSuccess = False 

    min1 = ToolConfig.configJson['grab_min_interval']
    max1 = ToolConfig.configJson['grab_max_interval']

    tmsleep = random.randrange(min1,max1)/1000.0

    moneyResult = 0
    
    errorSleep2 = 10
    for retry in range(3):
        # logging.info(f"grab ::::: {curl2}")
        res2=HttpUtil.curl2requests(curl2)

        moneyResult = 0

        try:
            resp18=requests.request(
                method=res2.method,
                url=res2.fullurl,
                data=res2.data,
                json=res2.json,
                headers=res2.headers
            )
        except Exception as err2:
            logging.warn(f"采集重试。原因：请求报表子系统的结算明细数据(psnNo = {psnNo}) 出现异常,稍后重试。")
            time.sleep(errorSleep2)
            continue

    
        if resp18.status_code != 200:
            logging.warn(f"采集重试。原因：请求报表子系统的结算明细数据(psnNo = {psnNo})，response.status_code={resp18.status_code}， {resp18.text}")
            time.sleep(errorSleep2)
            continue
        else:
            strjson = resp18.text
            json18 = json.loads(strjson)

            print(strjson)

            if json18['code']!=0 or json18['type']!='success':
                logging.warn(f"请求的结果：{json18}")
                
                time.sleep(tmsleep)
                continue
            else:
                requestSuccess=True
            
    if not requestSuccess:
        logging.error(f"终止采集{psnNo}的活动，经过3次重视依旧失败。")
        return moneyResult

    pageCount8 = json18['data']['pages']
    recordCounts8 = json18['data']['recordCounts']

    data18s = json18['data']['data']

    for data18 in data18s:
        psnNo8=data18['psnNo']
        strSetlTime=data18['setlTime']
        setlTime8 = strSetlTime

        setlId8= None
        psnName8=data18['psnName']
        insuAdmdvsName8 = data18['insuAdmdvs']
        insutype8=data18['insutype']
        insutypeName8=''
        medType8=data18['medType']
        fixmedinsCode8=data18['fixmedinsCode']

        fixmedinsName8=data18['fixmedinsName']

        cardNo8 = data18['certno']
        age8= IdCardUtil.calc_age_from_id(cardNo8)
        gend8=data18['gend']

        diseName8=None
        diseNo8=None
        
        begndate8= data18['begntime']
        enddate8= setlTime8
        
        hiAgreSumfee8=data18['hiAgreSumfee']
        medfeeSumamt8 = data18['medfeeSumamt']

        if medfeeSumamt8 != 0.00:
            hiAgreSumfee8 = medfeeSumamt8

        inscpAmt8=data18['inscpAmt']
        crtDedc8=data18['crtDedc']
        fundPaySumamt8=data18['fundPaySumamt']

        hifpPay8=data18['hifpPay']
        hifmiPay8=data18['hifmiPay']
        hifobPay8=data18['hifobPay']
        mafPay8=data18['mafPay']
        cvlservPay8=data18['cvlservPay']
        othPay8=data18['othfundPay']
        hifesPay8=data18['hifesPay']
        fulamtOwnpayAmt8=data18['fulamtOwnpayAmt']
        psnPay8=data18['psnPay']
        acctPay8=data18['acctPay']

        acctMulaidPay8= None
        cashPayamt8=data18['cashPayamt']
        poolPropSelfpay8=data18['poolPropSelfpay']
        refdSetlFlag8=data18['refdSetlFlag']


        params={ "id":uuid.uuid4().hex, "setl_trans_numb": setlId8, "psnNo":psnNo8,
                "psnName": psnName8, "setlTime":setlTime8,\
                "insuAdmdvs":'',"insuAdmdvsName":insuAdmdvsName8,\
                "insutype":insutype8, "insutypeName":insutypeName8, \
                "medType":medType8,"medTypeName":'',"fixmedinsCode":fixmedinsCode8,\
                "fixmedinsName":fixmedinsName8, "cardNo": cardNo8 , \
            "age":age8,"gend":gend8,"diseName":diseName8,"diseNo":diseNo8,\
            "begndate":begndate8,"enddate":enddate8,\
            
            "hiAgreSumfee":hiAgreSumfee8,"inscpAmt":inscpAmt8,"crtDedc":crtDedc8,\
            "fundPaySumamt":fundPaySumamt8,"hifpPay":hifpPay8,"hifmiPay":hifmiPay8,"hifobPay":hifobPay8,\
            "mafPay":mafPay8,"cvlservPay":cvlservPay8,"othPay":othPay8,\
            "hifesPay":hifesPay8,"fulamtOwnpayAmt":fulamtOwnpayAmt8,"psnPay":psnPay8,\
            "acctPay":acctPay8,"acctMulaidPay":acctMulaidPay8,"cashPayamt":cashPayamt8,\
            "poolPropSelfpay":poolPropSelfpay8,"refdSetlFlag":refdSetlFlag8   }
        
        _updateJiesuanMingxiParam(params)

        _save_jiesuan_mingxi(psnNo8, setlTime8, params)

        moneyResult += hiAgreSumfee8

        # logging.info(f"*****采集报表子系统的结算明细数据 {recordCounts8}条记录")
    return moneyResult


if __name__ == '__main__':  

    doGrabAllExcel='n' 
    doGrabYidiMingxiExcel='n' 
    doGrabBendiMingxi ='n'

    doGrabAllJiesuanMingxi = 'n'


    init_tool()

    # 检查并初始化
    checkConfigJson()

    doGrabAllExcel = input(f" ? ? 是否需要采集报表子系统导出的结算数据，数据来源为<{ToolConfig.configJson['benqi_all_excel']}>和<{ToolConfig.configJson['shangqi_all_excel']}>(y/n)：").strip().lower() 
    if doGrabAllExcel !='y':
        doGrabAllExcel = "n"

    doGrabYidiMingxiExcel = input(f" ? ? 是否需要采集异地子系统导出的结算明细数据，数据来源为<{ToolConfig.configJson['benqi_yidi_mingxi_excel']}>(y/n)：").strip().lower() 
    if doGrabYidiMingxiExcel !='y':
        doGrabYidiMingxiExcel = "n"

    doGrabBendiMingxi = input(f" ? ? 是否需要采集定点医疗机构系统的结算明细数据，数据来源为<{ToolConfig.yb2siteUrl}> (y/n)：").strip().lower() 
    if doGrabBendiMingxi !='y':
        doGrabBendiMingxi = "n"
        


    initSqlHelper()

    # 1 创建任务
    step1_create_task()

    if doGrabBendiMingxi =='y':
        if "bendiMingxiJindu" in ToolConfig.TaskStatusJson \
              and len(ToolConfig.TaskStatusJson['bendiMingxiJindu'])>0:       
            ToolConfig.doGrabBendiMingxiJixu = input(f" ? ? ? ? 是否继续之前的定点医疗机构系统的结算数据采集位置？y:将从之前的位置继续，n：将从头采集(y/n):").strip().lower() 
            if ToolConfig.doGrabBendiMingxiJixu =='n':
                ToolConfig.doGrabBendiMingxiJixu = input(f" ? ? ? ? ? ? 再问一遍： 是否继续之前的定点医疗机构系统的结算数据采集位置？y:将从之前的位置继续，n：将从头采集(y/n):").strip().lower() 
                
    if ToolConfig.doGrabBendiMingxiJixu =='n':

        # 清除之前定点医疗机构数据采集的进度
        ToolConfig.TaskStatusJson['bendiMingxiJindu']={}
        updateTaskStatusInfo()


    doGrabAllJiesuanMingxi = input(f" ? ? 是否从报表子系统根据结算总额差异，采集的结算明细数据，数据来源为<{ToolConfig.yb1siteUrl}> (y/n)：").strip().lower() 
    if doGrabAllJiesuanMingxi !='y':
        doGrabAllJiesuanMingxi = "n"
    else:
        # 当决定从报表子系统采集数据的时候，要验证是否从之前的采集记录继续采集
        if "allMingxiPsnNo" in ToolConfig.TaskStatusJson and \
            ToolConfig.TaskStatusJson['allMingxiPsnNo']!='':
            ToolConfig.doGrabAllMingxiJixu = input(f" ? ? ? ? 之前采集到了psnNo为 {ToolConfig.TaskStatusJson['allMingxiPsnNo']}, 是否继续？ y：从该psnNo编号继续，n：从头采集。(y/n): ").strip().lower()
            if ToolConfig.doGrabAllMingxiJixu == 'n':
                ToolConfig.doGrabAllMingxiJixu = input(f" ? ? ? ? 再问一遍：之前采集到了psnNo为 {ToolConfig.TaskStatusJson['allMingxiPsnNo']}, 是否继续？ y：从该psnNo编号继续，n：从头采集。(y/n): ").strip().lower()

    if ToolConfig.doGrabAllMingxiJixu =='n':
        ToolConfig.TaskStatusJson['allMingxiPsnNo']=''
        updateTaskStatusInfo()



    # 清除临时数据
    if doGrabAllExcel=='y':
        clear_all_excel()
        # 2 采集本期所有的数据，对应 config.json 的 benqi_all_excel 
        step2_grab_benqi_all_execl()
        step3_grab_shangqi_all_execl()

    if doGrabYidiMingxiExcel=='y':
        clear_yidi_mingxi_excel()
        step4_grab_benqi_yidi_mingxi_execl()

        # 3 将 异地结算明细数据，保存到 t_ybtool1_jiesuan_mingxi 
        step6_save_yidi_jiesuan_mingxi()

    # 初始化 chromedriver
    if doGrabBendiMingxi=='y' or doGrabAllJiesuanMingxi == 'y':
        initChromeDriver()

    if doGrabBendiMingxi=='y':
        step11_grab_benqi_bendi_mingxi()

    if doGrabAllJiesuanMingxi=='y':
        step12_grab_all_person_jiesuan()

    # 最后关闭 chromedriver
    time.sleep(10)
    SeleniumUtil.closeDriver(ToolConfig.chromeDriver)



